package org.ielts.repository;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.ArrayUtils;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.classic.Session;
import org.ielts.domain.base.SignCourse;
import org.ielts.domain.base.Student;
import org.ielts.domain.report.StudentQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.yifeng.dao.HibernateRepository;

@Repository
public class SignCourseRepository extends HibernateRepository<SignCourse, String> {

	public int syncUsedTimes() {
		String hql = "update SignCourse c set c.usedTimes=(select count(l.id) from OrderLessonLog l where l.signCourse.id=c.id and l.valid=true)";

		int result = hibernateTemplate.bulkUpdate(hql);
		return result;
	}

	public Page<SignCourse> findByRemainTimes(StudentQuery studentQuery, long remainTimes, Pageable pageable) {
		String hql = "SELECT new SignCourse(s, (SELECT Count(*) FROM OrderLessonLog o WHERE o.valid = true AND o.signCourse.id = s.id) as usedTimes ) "
				+ "FROM SignCourse s WHERE s.times > 0 AND (s.times - "
				+ "(SELECT Count(*) FROM OrderLessonLog o WHERE o.valid = true AND o.signCourse.id = s.id)) <= :remainTimes ";

		String hql1 = "SELECT count(s) FROM SignCourse s WHERE s.times > 0 AND (s.times - "
				+ "(SELECT Count(*) FROM OrderLessonLog o WHERE o.valid = true AND o.signCourse.id = s.id)) <= :remainTimes ";

		if (studentQuery.hasConsultStarred()) {
			hql += "AND s.student.consultStarred in :starred";
			hql1 += "AND s.student.consultStarred in :starred";
		}
		if (studentQuery.hasConsultant()) {
			hql += "AND s.student.consultor.id in :consultants";
			hql1 += "AND s.student.consultor.id in :consultants";
		}

		Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
		Query query = session.createQuery(hql);
		query.setParameter("remainTimes", remainTimes);
		if (studentQuery.hasConsultStarred()) {
			query.setParameterList("starred", ArrayUtils.toObject(studentQuery.getConsultStarred()));
		}
		if (studentQuery.hasConsultant()) {
			query.setParameterList("consultants", studentQuery.getConsultant());
		}
		query.setFirstResult(pageable.getOffset());// 定义从第几条开始查询
		query.setMaxResults(pageable.getPageSize());// 定义返回的记录数

		@SuppressWarnings("unchecked")
		List<SignCourse> list = query.list();

		Query totalQuery = session.createQuery(hql1);
		totalQuery.setParameter("remainTimes", remainTimes);
		if (studentQuery.hasConsultStarred()) {
			totalQuery.setParameterList("starred", ArrayUtils.toObject(studentQuery.getConsultStarred()));
		}
		if (studentQuery.hasConsultant()) {
			totalQuery.setParameterList("consultants", studentQuery.getConsultant());
		}

		long total = (Long) totalQuery.uniqueResult();

		return new PageImpl<SignCourse>(list, pageable, total);
	}

	public Page<SignCourse> findByRemainDate(StudentQuery studentQuery, Date endTime, Pageable pageable) {
		String hql = "SELECT s " + "FROM SignCourse s WHERE s.endTime < :endTime ";

		String hql1 = "SELECT count(s) FROM SignCourse s WHERE s.endTime < :endTime  ";

		if (studentQuery.hasConsultStarred()) {
			hql += "AND s.student.consultStarred in :starred";
			hql1 += "AND s.student.consultStarred in :starred";
		}
		if (studentQuery.hasConsultant()) {
			hql += "AND s.student.consultor.id in :consultants";
			hql1 += "AND s.student.consultor.id in :consultants";
		}

		Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
		Query query = session.createQuery(hql);
		query.setParameter("endTime", endTime);
		if (studentQuery.hasConsultStarred()) {
			query.setParameterList("starred", ArrayUtils.toObject(studentQuery.getConsultStarred()));
		}
		if (studentQuery.hasConsultant()) {
			query.setParameterList("consultants", studentQuery.getConsultant());
		}
		query.setFirstResult(pageable.getOffset());// 定义从第几条开始查询
		query.setMaxResults(pageable.getPageSize());// 定义返回的记录数

		@SuppressWarnings("unchecked")
		List<SignCourse> list = query.list();

		Query totalQuery = session.createQuery(hql1);
		totalQuery.setParameter("endTime", endTime);
		if (studentQuery.hasConsultStarred()) {
			totalQuery.setParameterList("starred", ArrayUtils.toObject(studentQuery.getConsultStarred()));
		}
		if (studentQuery.hasConsultant()) {
			totalQuery.setParameterList("consultants", studentQuery.getConsultant());
		}

		long total = (Long) totalQuery.uniqueResult();

		return new PageImpl<SignCourse>(list, pageable, total);
	}
}
